package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Model.Student;
import Util.ConnectionUtil;

public class StudentDao {
	/**
	 * 查询学生列表
	 */
	public static ArrayList<Student> queryStudentList() {
		ArrayList<Student> studentList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;
		try {
			// 执行查询
			System.out.println(" 实例化Statement对象...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT * FROM student";
			ResultSet rs = stmt.executeQuery(sql);

			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				String classname = rs.getString("classname");
				String major = rs.getString("major");
				int classid = rs.getInt("classid");

				Student student = new Student(id, name, age, classname, major, classid);
				studentList.add(student);
			}
		} catch (SQLException se) {
			// 处理 JDBC 错误
			se.printStackTrace();
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
		}
		System.out.println("查询成功!");
		return studentList;

	}

	/*
	 * //插入学生 public static void addUseStatement(Student student) { Connection
	 * connection = ConnectionUtil.getConnection(); int classid =
	 * ClassDao.getClassId(student.getClassname()); try { Statement statement =
	 * connection.createStatement();
	 * 
	 * String
	 * sql="INSERT INTO student VALUES ("+student.getId()+", '"+student.getName(
	 * )+"', "+student.getAge()+", '"+student.getClassname()+"', '"+student.
	 * getMajor()+"', "+student.getClassid()+"); "; System.err.println(sql);
	 * statement.execute(sql);
	 * 
	 * } catch (SQLException e) { // TODO Auto-generated catch block
	 * e.printStackTrace(); }
	 * 
	 * }
	 */
	public static void addUsePrepareStatement(Student student) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into student values(?,?,?,?,?,?)";
		try {
			PreparedStatement psta = conn.prepareStatement(sql);
			psta.setInt(1, student.getId());
			psta.setString(2, student.getName());
			psta.setInt(3, student.getAge());
			psta.setString(4, student.getClassname());
			psta.setString(5, student.getMajor());
			int classid = ClassDao.getClassId(student.getClassname());
			psta.setInt(6, classid);

			// int gender = 1;
			// if(student.getGender().equals("男") ) {
			// gender = 1;
			// }else {
			// gender = 0;
			// }
			// psta.setInt(5, gender);

			int result = psta.executeUpdate();
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	// 根据学生姓名删除学生
	public static void del(String name) {
		Connection conn = ConnectionUtil.getConnection();

		String sql = "delete from student where name = '" + name + "'";

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void update(String name, int column, String value) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = null;
		switch (column) {
		case 1:
			sql = "update student SET name = '" + value + "'  where name = '" + name + "'";
			break;

		case 2:
			sql = "update student SET age = '" + value + "'  where name = '" + name + "'";
			break;

		case 3:
			sql = "update student SET classname = '" + value + "'  where name = '" + name + "'";
			break;

		case 4:
			sql = "update student SET major = '" + value + "'  where name = '" + name + "'";
			break;

		case 5:
			sql = "update student SET classid = '" + value + "'  where name = '" + name + "'";
			break;

		default:
			break;
		}

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static int getStuIdWithName(String name) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "select id from student where name = '" + name + "'";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			if (rs.next()) {
				int id = rs.getInt(1);
				return id;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 0000;
	}
}
